import pandas as pd
import requests
import json
import urllib
import pymysql
import openpyxl

# 请求头
headers = {
    'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/80.0.3987.163 Safari/537.36'
}
url = 'https://api.inews.qq.com/newsqa/v1/query/inner/publish/modules/list?modules=chinaDayList,chinaDayAddList,cityStatis,nowConfirmStatis,provinceCompare'
wb = openpyxl.Workbook()
sht = wb.create_sheet("中国日疫情表")
sht.cell(row=1, column=1).value = "日期"
sht.cell(row=1, column=2).value = "死亡比例"
sht.cell(row=1, column=3).value = "治愈病例"
sht.cell(row=1, column=4).value = "疑似病例"
sht.cell(row=1, column=5).value = "累计确诊"
sht.cell(row=1, column=6).value = "累计死亡"
sht.cell(row=1, column=7).value = "累计治愈"
sht.cell(row=1, column=8).value = "境外输入"
sht.cell(row=1, column=9).value = "无症状者"
sht.cell(row=1, column=10).value = "现在确诊"
sht.cell(row=1, column=11).value = "新增确诊"
sht.cell(row=1, column=12).value = "新增死亡"
sht.cell(row=1, column=13).value = "新增治愈"

sess = requests.Session()
# 破解封IP反爬机制
r = sess.get(url=url, headers=headers, proxies={"https://": '222.110.147.50:3128'}).text
res = json.loads(r, strict=False)['data']
res_list = res['chinaDayList']

# 现在十分严重的
list_nowSevere = []
# 死亡比例
list_deadRate = []
# 治愈比例
list_healRate = []
# 日期
list_date = []
# 确诊
list_confirm = []
# 疑似病例
list_suspect = []
# 死亡
list_dead = []
# 治愈
list_heal = []
# 现确诊
list_nowConfirm = []
# 境外输入
list_importedCase = []
# 无症状感染者
list_noInfect = []
# 总数据
list_sum1 = []
for i in range(len(res_list)):
    list1 = []
    # 编号
    list1.append(i + 1)
    # 日期
    date = res_list[i]['date']
    list1.append(date)
    list_date.append(date)
    # 现在十分严重的
    # nowSevere = res_list[i]['nowSevere']
    # list1.append(nowSevere)
    # list_nowSevere.append(nowSevere)
    # 死亡比例
    deadRate = res_list[i]['deadRate']
    list1.append(deadRate)
    list_deadRate.append(deadRate)

    # 治愈比例
    healRate = res_list[i]['healRate']
    list1.append(healRate)
    list_healRate.append(healRate)

    # 疑似病例
    suspect = res_list[i]['suspect']
    list1.append(suspect)
    list_suspect.append(suspect)
    # 确诊
    confirm = res_list[i]['confirm']
    list1.append(confirm)
    list_confirm.append(confirm)
    # 死亡
    dead = res_list[i]['dead']
    list1.append(dead)
    list_dead.append(dead)
    # 治愈
    heal = res_list[i]['heal']
    list1.append(heal)
    list_heal.append(heal)
    # 境外输入
    importedCase = res_list[i]['importedCase']
    list1.append(importedCase)
    list_importedCase.append(importedCase)
    # 无症状感染者
    noInfect = res_list[i]['noInfect']
    list1.append(noInfect)
    list_noInfect.append(noInfect)
    # 现确诊
    nowConfirm = res_list[i]['nowConfirm']
    list1.append(nowConfirm)
    list_nowConfirm.append(nowConfirm)
    list_sum1.append(list1)

res_list1 = res['chinaDayAddList']
# 日期
list_date = []
# 每日新增
list_confirmadd = []
# 每日死亡
list_deadadd = []
# 每日治愈
list_healadd = []
# 总数据每日新增  用于预测新增
list_sum2 = []
# 总数据每日死亡  用于预测死亡
list_sum3 = []
# 总数据每日治愈  用于预测治愈
list_sum4 = []
for i in range(len(res_list1)):
    list2 = []
    list3 = []
    list4 = []
    # 日期
    date = res_list1[i]['date']
    date1 = date.split('.')
    date2 = '2022' + date1[0] + date1[1]
    list2.append(date2)
    list_date.append(date2)
    # 每日新增
    confirmadd = res_list1[i]['confirm']
    list2.append(confirmadd)
    list_confirmadd.append(confirmadd)
    # 每日死亡
    deadadd = res_list1[i]['dead']
    list2.append(deadadd)
    list_deadadd.append(deadadd)
    # 每日治愈
    healadd = res_list1[i]['heal']
    list2.append(healadd)
    list_healadd.append(healadd)

    list_sum2.append(list2)

# 把列表的数据转换成DataFrame类型的数据
div_list1 = pd.DataFrame(list_sum1[6:]
                         , columns=['编号', '日期', '死亡比例', '治愈比例', '疑似病例',
                                    '累计确诊', '累计死亡', '累计治愈', '境外输入', '无症状感染者', '现确诊'])
div_list2 = pd.DataFrame(list_sum2
                         , columns=['日期', '每日新增确诊', '每日新增死亡', '每日新增治愈'])
# print(div_list1)
# print(div_list2)


conn = pymysql.connect(
    host="127.0.0.1",
    port=3306,  # 端口号
    user="root",  # 数据库用户
    password="123456",  # 数据库密码
    database="bysj"  # 要连接的数据库名称
)
curson = conn.cursor()

# # 查询数据库
curson.execute('select id from bysj.chinadaylist')
id_list = curson.fetchall()
formatList = list(set(list_date))
formatList.sort(key=list_date.index)
for i in range(1,len(formatList)):
    sht.cell(row=i+1, column=1).value = list_date[i]
    sht.cell(row=i+1, column=2).value = list_deadRate[i]
    sht.cell(row=i+1, column=3).value = list_healRate[i]
    sht.cell(row=i+1, column=4).value = list_suspect[i]
    sht.cell(row=i+1, column=5).value = list_confirm[i]
    sht.cell(row=i+1, column=6).value = list_dead[i]
    sht.cell(row=i+1, column=7).value = list_heal[i]
    sht.cell(row=i+1, column=8).value = list_importedCase[i]
    sht.cell(row=i+1, column=9).value = list_noInfect[i]
    sht.cell(row=i+1, column=10).value = list_nowConfirm[i]
    sht.cell(row=i+1, column=11).value = list_confirmadd[i]
    sht.cell(row=i+1, column=12).value = list_deadadd[i]
    sht.cell(row=i+1, column=13).value = list_healadd[i]


# 存入数据库

for i in range((len(formatList) - abs(len(id_list) - len(formatList))), len(formatList)):
    curson.execute('insert into bysj.chinadaylist(date,deadRate,healRate,suspect,confirm,dead,heal,importedCase,noInfect,nowConfirm,confirmadd,deadadd,healadd )values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)',
        [list_date[i], list_deadRate[i], list_healRate[i], list_suspect[i], list_confirm[i],
         list_dead[i], list_heal[i], list_importedCase[i], list_noInfect[i], list_nowConfirm[i], list_confirmadd[i],
         list_deadadd[i], list_healadd[i]])

wb.save(r'E:\bysj\diango\exc\中国日疫情.xlsx')
conn.commit()
